USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'InsUpdOpportunityProductDetails')
	BEGIN
		DROP  Procedure  dbo.InsUpdOpportunityProductDetails
	END
GO

CREATE Procedure dbo.InsUpdOpportunityProductDetails (
	@OPPORTUNITYPRODID		VARCHAR(10)
,	@PRODUCTID				VARCHAR(10)
,	@QUANTITY				VARCHAR(10)
,	@CUSTOMPRICE			VARCHAR(20)
,	@OPPORTUNITYGUID		VARCHAR(100)
)
AS
BEGIN

	IF (CAST(@OPPORTUNITYPRODID AS INT) = 0) AND EXISTS(SELECT 1 FROM TR_OPPORTUNITYI WHERE PRODUCTID = @PRODUCTID AND OppGUID = @OPPORTUNITYGUID)
	BEGIN
		SELECT 
			'<DATA>
				<RESPONSE>
					<RESULT>E</RESULT>
					<MESSAGE><![CDATA[Product already exists]]></MESSAGE>
				</RESPONSE>
			</DATA>' AS Result
		RETURN
	END

	IF ( CAST(@Quantity AS INT) = 0 ) 
	BEGIN
		-- DELETE THE PRODUCT FOR GIVEN OPPORTUNITY
		DELETE FROM TR_OPPORTUNITYI
		WHERE 
			OppGUID = @OpportunityGUID
		AND	ProductID = @ProductID
		AND @Quantity = 0

		SELECT 
			'<DATA>
				<RESPONSE>
					<RESULT>S</RESULT>
					<MESSAGE><![CDATA[Opportunity Product saved successfully]]></MESSAGE>
				</RESPONSE>
			</DATA>' AS Result
		RETURN
	END

	BEGIN TRAN
		BEGIN TRY
			IF EXISTS(SELECT 1 FROM TR_OPPORTUNITYI WHERE OPPORTUNITYID = @OPPORTUNITYPRODID AND PRODUCTID = @PRODUCTID AND CAST(OPPGUID AS VARCHAR(40)) = @OPPORTUNITYGUID) 
			BEGIN		-- UPDATE
				UPDATE TR_OPPORTUNITYI
				SET	QUANTITY			= @QUANTITY
				,	CUSTOMPRICE			= NULLIF(@CUSTOMPRICE,'')
				WHERE
					OPPORTUNITYID					= @OPPORTUNITYPRODID
				AND CAST(OPPGUID AS VARCHAR(40))	= @OPPORTUNITYGUID
				AND	PRODUCTID						= @PRODUCTID
			END
			ELSE
			BEGIN		-- INSERT
				SELECT @OPPORTUNITYPRODID	= ISNULL(MAX(OPPORTUNITYID), 0) +1 FROM TR_OPPORTUNITYI

				INSERT INTO TR_OPPORTUNITYI (OpportunityID, ProductId, Quantity, CustomPrice, OppGuid)
				VALUES(
					@OPPORTUNITYPRODID
				,	@PRODUCTID		
				,	@QUANTITY
				,	NULLIF(@CustomPrice, '')
				,	@OPPORTUNITYGUID
				)
			END

			COMMIT TRAN

			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>S</RESULT>
						<MESSAGE>Opportunity Product details saved successfully</MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END TRY
		BEGIN CATCH
			ROLLBACK TRAN
			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>E</RESULT>
						<MESSAGE><![CDATA[Unable to save Opportunity Product Details. '+ERROR_MESSAGE()+']]></MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END CATCH
END
GO
